# added by yinchao, Fri July 05 11:09	2024
# many to many relationship in database.

import json
import sqlite3
data = """
[
    ["Charley", "si110"], 
    ["Mea", "si110"],
    ["Hattie", "si110"],
    ["Keziah", "si110"],
    ["Mea", "si106"],
    ["Rosa", "si106"],
    ["Mairin", "si106"],
    ["Zendel", "si106"],
    ["Honie", "si106"],
    ["Rosa", "si106"]
]
"""

with sqlite3.connect('many2many.db3') as conn:
    cursor = conn.cursor()

    data = json.loads(data)
    for item in data:
        name, course = item[0], item[1]
        # print(name, course)

        cursor.execute('INSERT OR IGNORE INTO Course (title) VALUES (?)', (course, ))
        cursor.execute('SELECT id FROM Course WHERE title = ?', (course, ))
        course_id = cursor.fetchone()[0]

        cursor.execute('INSERT OR IGNORE INTO User (name) VALUES (?)', (name, ))
        cursor.execute('SELECT id FROM User WHERE name = ?', (name, ))
        user_id = cursor.fetchone()[0]

        cursor.execute(
            'INSERT OR IGNORE INTO Member (user_id, course_id) VALUES (?, ?)', (user_id, course_id))

    conn.commit()
